package com.emisky.pictures.common;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.alibaba.druid.pool.DruidDataSourceFactory;



public class QueryHelper {
	private final static QueryRunner _runner=new QueryRunner();
	private static DataSource _dataSource=null;
	static {
		Properties properties =new Properties();
		try {
			properties.load(QueryHelper.class.getClassLoader().getResourceAsStream("dbconfig.properties"));
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
        try {
        	_dataSource =DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
	}
	/**
	 * 获取数据源
	 * @return
	 */
	private static DataSource getDataSource(){
		return _dataSource;
	}
	/**
	 * 获取数据库链接
	 * @return
	 */
	private static Connection getConnection(){
		Connection conn=null;
		try {
			conn= getDataSource().getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} 
		return conn;
	}	
	/**
	 * 执行INSERT/UPDATE/DELETE语句
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(String sql,Object...params){
		int result=0;
		Connection conn=null;
		try {
			conn=getConnection();
			result= _runner.update(conn, sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	/**
	 * 批量执行SQL语句
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int[] batch(String sql, Object[][] params){
		Connection conn=null;
		try{
			conn=getConnection();
            return _runner.batch(conn, sql, params);
        }catch(SQLException e){
            throw new RuntimeException(e);
        }finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * dbutil 查询参数
	 * @param sql
	 * @param rsh
	 * @param params
	 * @return
	 */
	public  static <T> T query(String sql,ResultSetHandler<T> rsh,Object...params){
		Connection conn=null;
		try {		
			conn=getConnection();
			return _runner.query(conn, sql, rsh, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	public  static <T> List<T> queryList(Class<T> clazz,String sql,Object...params){
		ResultSetHandler<List<T>> h = new BeanListHandler<T>(clazz);
		return query(sql,h,params);
	}
	
	public static <T> T queryBean(Class<T> clazz, String sql,Object...params){
		ResultSetHandler<T> h = new BeanHandler<T>(clazz);
		return query(sql,h,params);
	}
	
	public static <T> T queryScalar(String sql,Object...params){
		ResultSetHandler<T> h = new ScalarHandler<T>();
		return query(sql,h,params);
	}
}
